package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDALocation;
import ro.gateway.aida.obj.Country;
import ro.gateway.aida.obj.County;
import ro.gateway.aida.obj.Region;
import ro.gateway.aida.obj.Town;
import ro.gateway.aida.servlet.Constants;


/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 *
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: TownDB.java,v 1.2 2005/01/26 10:35:58 mihaipostelnicu Exp $
 */
public class TownDB extends DBPersistenceManager {

	private TownDB(PersistenceToken token) {
		super(token);
	}

	public static TownDB getManager(PersistenceToken token) {
		return new TownDB(token);
	}

	public void deleteTown(int sirsup_id)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_TOWN);
			ps.setInt(1, sirsup_id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			throw e;
		} finally {
			con.close();
		}
	}

	public void updateTown(Town town)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(UPDATE_TOWN);
			ps.setInt(1, town.getType());
			ps.setString(2, town.getName());
			ps.setDouble(3, town.getGeo_data().getLatitude());
			ps.setDouble(4, town.getGeo_data().getLongitude());
			ps.setString(5, town.getGeo_data().getGeo_code());
			ps.setInt(6, town.getSirsup_id());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public void insertTown(Town town)
		 throws SQLException {
		if (town.getSirsup_id() == -1) {
			town.setSirsup_id((int) db_token.getIdgenerators()
				 .nextVal(Constants.IDGEN_TOWNS));
		}
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_TOWN);
			ps.setInt(1, town.getSirsup_id());
			ps.setInt(2, town.getType());
			ps.setInt(3, town.getJudet_id());
			ps.setString(4, town.getName());
			ps.setDouble(5, town.getGeo_data().getLatitude());
			ps.setDouble(6, town.getGeo_data().getLongitude());
			ps.setString(7, town.getGeo_data().getGeo_code());
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public Town getById(int sirsup_id)
		 throws SQLException {
		Connection con = getConnection();
		Town result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_BY_ID);
			ps.setInt(1, sirsup_id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new Town();
				result.setSirsup_id(sirsup_id);
				result.setType(rs.getByte(1));
				result.setName(rs.getString(2));
				result.setJudet_id(rs.getInt(3));
				result.getGeo_data().setLatitude(rs.getDouble(4));
				result.getGeo_data().setLongitude(rs.getDouble(5));
				result.getGeo_data().setGeo_code(rs.getString(6));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public Town[] getAllForCounty(int judet_id)
		 throws SQLException {

		Connection con = getConnection();
		ArrayList items = new ArrayList();
		Town[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(GET_ALL_FOR_JUDET);
			ps.setInt(1, judet_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Town item = new Town();

				item.setSirsup_id(rs.getInt(1));
				item.setType(rs.getByte(2));
				item.setName(rs.getString(3));
				item.setJudet_id(judet_id);

				items.add(item);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new Town[items.size()];
			items.toArray(result);
		}

		return result;
	}

	public AIDALocation[] findTowns(String what,
							  int max_result_len)
		 throws SQLException {
		if (what == null) return null;
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		AIDALocation[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(FIND_TOWNS);
			ps.setString(1, what.toUpperCase() + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				Country country = new Country();
				Region region = new Region();
				County county = new County();
				Town town = new Town();

//                        "SELECT t.sirsup_id,t.type,t.judet_id,t.name," +
//                        "cn.id, cn.regiune_id, cn.name, " +
//                        "r.country_iso3, r.name, " +
//                        "comparator.name " +
				town.setSirsup_id(rs.getInt(1));
				town.setType(rs.getByte(2));
				town.setJudet_id(rs.getInt(3));
				town.setName(rs.getString(4));
				county.setId(rs.getInt(5));
				county.setRegiune_id(rs.getInt(6));
				county.setName(rs.getString(7));
				county.setId(town.getJudet_id());
				region.setCountryIso3(rs.getString(8));
				region.setName(rs.getString(9));
				region.setId(county.getRegiune_id());
				country.setName(rs.getString(10));
				country.setIso3(region.getCountryIso3());

				item.setCountry(country);
				item.setRegion(region);
				item.setJudet(county);
				item.setOras(town);

				items.add(item);

				if (items.size() > max_result_len) break;
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public AIDALocation[] findTownsIdentical(String what,
									 int max_result_len)
		 throws SQLException {
		if (what == null) return null;
		Connection con = getConnection();
		ArrayList items = new ArrayList();
		AIDALocation[] result = null;

		try {
			PreparedStatement ps = con.prepareStatement(FIND_TOWNS);
			ps.setString(1, what.toUpperCase());
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				AIDALocation item = new AIDALocation();

				Country country = new Country();
				Region region = new Region();
				County county = new County();
				Town town = new Town();

//                        "SELECT t.sirsup_id,t.type,t.judet_id,t.name," +
//                        "cn.id, cn.regiune_id, cn.name, " +
//                        "r.country_iso3, r.name, " +
//                        "comparator.name " +
				town.setSirsup_id(rs.getInt(1));
				town.setType(rs.getByte(2));
				town.setJudet_id(rs.getInt(3));
				town.setName(rs.getString(4));
				county.setId(rs.getInt(5));
				county.setRegiune_id(rs.getInt(6));
				county.setName(rs.getString(7));
				county.setId(town.getJudet_id());
				region.setCountryIso3(rs.getString(8));
				region.setName(rs.getString(9));
				region.setId(county.getRegiune_id());
				country.setName(rs.getString(10));
				country.setIso3(region.getCountryIso3());

				item.setCountry(country);
				item.setRegion(region);
				item.setJudet(county);
				item.setOras(town);

				items.add(item);

				if (items.size() > max_result_len) break;
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (items.size() > 0) {
			result = new AIDALocation[items.size()];
			items.toArray(result);
		}
		return result;
	}

	public long getLastId()
		 throws SQLException {
		Connection con = db_token.getCFService().
			 getConnection(db_token.getPool_name());
		long result = 0;

		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	private static final String FIND_TOWNS =
		 "SELECT t.sirsup_id,t.type,t.judet_id,t.name," +
		 "cn.id, cn.regiune_id, cn.name, " +
		 "r.country_iso3, r.name, " +
		 "c.name " +
		 "FROM towns t, regions r, counties cn, countries_iso3 c " +
		 "WHERE (c.iso3=r.country_iso3) " +
		 "AND (r.id=cn.regiune_id) " +
		 "AND (cn.id=t.judet_id) " +
		 "AND (t.name ilike ?)";
	public static final String DELETE_TOWN =
		 "delete from towns " +
		 "where sirsup_id=?";
	public static final String INSERT_TOWN =
		 "insert into towns (sirsup_id,type,judet_id,name,gs_lat,gs_long,geo_code) " +
		 "values (?,?,?,?,?,?,?)";
	public static final String UPDATE_TOWN =
		 "UPDATE towns SET type=?,name=?,gs_lat=?,gs_long=?,geo_code=? WHERE sirsup_id=?";
	public static final String GET_ALL_FOR_JUDET =
		 "SELECT sirsup_id, type, name " +
		 "FROM towns WHERE judet_id=? ORDER BY type ASC, name ASC";
	public static final String GET_BY_ID =
		 "SELECT type, name, judet_id, gs_lat, gs_long, geo_code FROM towns WHERE sirsup_id=?";
	public static final String GET_LAST_ID =
		 "SELECT max(sirsup_id) FROM towns";
}
